﻿using Dapper;
using IRepository;
using Entity;
using System;
using System.Data;
using System.Data.Common; // 引入DbConnection所在命名空间
using System.Threading.Tasks;
using System.Collections.Generic;
using Microsoft.Extensions.Logging;

namespace Repository
{
    public class StorageLocationRepository : IStorageLocationRepository
    {
        // 修正1：将具体类型SqlConnection改为抽象类型DbConnection
        private readonly DbConnection _dbConnection;
        private readonly ILogger<StorageLocationRepository> _logger;

        // 修正2：提供IDbConnection接口暴露（兼容需要此接口的场景）
        public IDbConnection GetDbConnection()
        {
            return _dbConnection; // DbConnection实现了IDbConnection，直接返回
        }

        // 修正3：构造函数参数类型改为DbConnection，与依赖注入类型匹配
        public StorageLocationRepository(DbConnection dbConnection, ILogger<StorageLocationRepository> logger)
        {
            _dbConnection = dbConnection ?? throw new ArgumentNullException(nameof(dbConnection));
            _logger = logger ?? throw new ArgumentNullException(nameof(logger));
        }

        public async Task<PaginationResult<StorageLocationEntity>> GetPagedListAsync(PaginationQuery query)
        {
            if (_dbConnection.State != ConnectionState.Open)
            {
                await _dbConnection.OpenAsync(); // DbConnection支持异步打开
            }

            // 明确指定is_delete属于库位表（sl.is_delete）
            var whereClause = "WHERE sl.is_delete = 0";

            // 关联用户表时，过滤有效用户
            var dataSql = $@"
        SELECT sl.*, u.user_id as users_id
        FROM wms_basic_storage_location sl
        LEFT JOIN sys_org_user_management u 
            ON sl.users_id = u.user_id 
            AND u.is_delete = 0  -- 仅关联未删除的用户
        {whereClause}
        ORDER BY sl.location_id
        OFFSET @SkipCount ROWS FETCH NEXT @PageSize ROWS ONLY";

            var data = (await _dbConnection.QueryAsync<StorageLocationEntity>(dataSql, new
            {
                SkipCount = query.SkipCount,
                PageSize = query.PageSize
            })).AsList();

            // 总数查询明确表别名
            var countSql = $"SELECT COUNT(*) FROM wms_basic_storage_location sl {whereClause}";
            var totalCount = await _dbConnection.ExecuteScalarAsync<int>(countSql);

            return new PaginationResult<StorageLocationEntity>
            {
                Data = data,
                TotalCount = totalCount,
                PageIndex = query.PageIndex,
                PageSize = query.PageSize
            };
        }

        public async Task DeleteAsync(int id)
        {
            _logger.LogInformation("开始删除库位，ID：{LocationId}", id);
            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }
                var sql = "UPDATE wms_basic_storage_location SET is_delete = 1 WHERE location_id = @Id";
                var affectedRows = await _dbConnection.ExecuteAsync(sql, new { Id = id });
                if (affectedRows == 0)
                {
                    _logger.LogWarning("删除库位失败，未找到ID为{LocationId}的库位", id);
                    throw new KeyNotFoundException($"未找到ID为{id}的库位");
                }
                _logger.LogInformation("库位删除成功，ID：{LocationId}", id);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "删除库位异常，ID：{LocationId}", id);
                throw;
            }
        }

        public async Task DeleteBatchAsync(int[] ids)
        {
            _logger.LogInformation("开始批量删除库位，ID列表：{Ids}", string.Join(",", ids));
            try
            {
                if (ids == null || ids.Length == 0)
                {
                    _logger.LogWarning("批量删除库位失败，ID列表为空");
                    throw new ArgumentException("批量删除的库位ID列表不能为空");
                }

                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }
                var sql = "UPDATE wms_basic_storage_location SET is_delete = 1 WHERE location_id IN @Ids";
                var affectedRows = await _dbConnection.ExecuteAsync(sql, new { Ids = ids });
                _logger.LogInformation("批量删除库位完成，影响行数：{AffectedRows}，ID列表：{Ids}",
                    affectedRows, string.Join(",", ids));
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "批量删除库位异常，ID列表：{Ids}", string.Join(",", ids));
                throw;
            }
        }

        public async Task<int> AddAsync(StorageLocationEntity entity)
        {
            _logger.LogInformation("开始新增库位，编号：{LocationNumber}", entity.location_number);
            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }

                // 校验库位编号是否已存在
                var existsSql = "SELECT COUNT(1) FROM wms_basic_storage_location WHERE location_number = @Number AND is_delete = 0";
                var exists = await _dbConnection.ExecuteScalarAsync<bool>(existsSql, new { Number = entity.location_number });
                if (exists)
                {
                    throw new InvalidOperationException($"库位编号{entity.location_number}已存在");
                }

                // 插入库位数据
                var sql = @"
            INSERT INTO wms_basic_storage_location 
            (warehouse_id, location_number, location_name, storage_location_capacity, users_id, 
             sorting, location_status, note, create_by, create_time, update_by, update_time, is_delete)
            VALUES 
            (@warehouse_id, @location_number, @location_name, @storage_location_capacity, @users_id, 
             @sorting, @location_status, @note, @create_by, @create_time, @update_by, @update_time, @is_delete);
            SELECT CAST(SCOPE_IDENTITY() AS INT);";

                var newId = await _dbConnection.QueryFirstAsync<int>(sql, entity);
                _logger.LogInformation("库位新增成功，ID：{NewId}，编号：{LocationNumber}", newId, entity.location_number);
                return newId;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "新增库位失败，编号：{LocationNumber}", entity.location_number);
                throw;
            }
        }
        // 新增：实现根据仓库ID获取库位ID列表
        public async Task<List<int>> GetIdsByWarehouseIdAsync(int warehouseId)
        {
            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }

                // 查询条件：关联指定仓库ID，且库位未删除（is_delete=0）
                var sql = @"
                    SELECT location_id 
                    FROM wms_basic_storage_location 
                    WHERE warehouse_id = @WarehouseId 
                      AND is_delete = 0";

                var locationIds = (await _dbConnection.QueryAsync<int>(sql, new { WarehouseId = warehouseId }))
                    .ToList();

                return locationIds;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询仓库关联的库位ID失败，仓库ID：{WarehouseId}", warehouseId);
                throw; // 抛出异常让上层处理
            }
        }
        public async Task<List<StorageLocationEntity>> GetByWarehouseIdAsync(int warehouseId)
        {
            try
            {
                if (_dbConnection.State != ConnectionState.Open)
                {
                    await _dbConnection.OpenAsync();
                }

                // 关键修正：去掉字段别名，直接使用数据库字段名（与实体类下划线属性匹配）
                var sql = @"
        SELECT 
            location_id,  -- 对应实体类的 location_id 属性
            location_name,  -- 对应实体类的 location_name 属性
            location_number,  -- 对应实体类的 location_number 属性
            warehouse_id,  -- 可选：如需返回仓库ID，可包含
            is_delete  -- 可选：如需验证删除状态，可包含
        FROM wms_basic_storage_location 
        WHERE 
            warehouse_id = @WarehouseId  -- 过滤指定仓库
            AND is_delete = 0  -- 排除已删除的库位
        ORDER BY location_name";  // 按名称排序

                // Dapper会自动将查询结果（字段名：location_id）映射到实体类的 location_id 属性
                var locations = (await _dbConnection.QueryAsync<StorageLocationEntity>(
                    sql,
                    new { WarehouseId = warehouseId }  // 参数匹配
                )).AsList();

                return locations;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "根据仓库ID查询仓库位失败，仓库ID：{WarehouseId}", warehouseId);
                throw;
            }
        }
    }
}